package com.klh;

import java.io.BufferedReader;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class Application {
    public static final String DB_URL = "jdbc:mysql://127.0.0.1:3306/demo?useSSL=false&serverTimezone=Asia/Shanghai";
    public static final String DB_USER = "";
    public static final String DB_PASSWORD = "";
    public static void main(String[] args) {

        // 日志文件路径
        String logFilePath = "D:/Computer/Downloads/mysql-slow.log";

        try (BufferedReader reader = new BufferedReader(new FileReader(logFilePath));
             Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD)) {

            String line;
            StringBuilder sqlQuery = new StringBuilder();
            while ((line = reader.readLine()) != null) {
                if (line.startsWith("# Time:")) {
                    // 解析日志信息
                    // 第一行
                    String logTime = parseLogTime(line);
                    // 第二行
                    line = reader.readLine();
                    Map<String, String> userInfoMap = parseUserInfo(line);
                    String user = userInfoMap.get("user");
                    String host = userInfoMap.get("host");
                    String sql_id = userInfoMap.get("sql_id");
                    // 第三行
                    line = reader.readLine();
                    double queryTime = parseQueryTime(line);
                    double lockTime = parseLockTime(line);
                    int rowsSent = parseRowsSent(line);
                    int rowsExamined = parseRowsExamined(line);
                    // 第四行
                    line = reader.readLine();
                    if (line.startsWith("use") && line.endsWith(";")){
                        // 排除干扰行
                        line = reader.readLine();
                    }
                    int timestamp = parseTimestamp(line);
                    // sql
                    do {
                        line = reader.readLine();
                        sqlQuery.append(line.trim());
                    }while (!line.contains(";"));

                    // 插入数据库
                    insertLog(conn, logTime, user, queryTime, lockTime, rowsSent, rowsExamined, sqlQuery.toString(), timestamp, host, sql_id);

                    // 清空 sqlQuery 为下一条日志准备
                    sqlQuery.setLength(0);
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // 解析 # Time: ...
    private static String parseLogTime(String line) {
        Pattern pattern = Pattern.compile("# Time: (.+)");
        Matcher matcher = pattern.matcher(line);
        if (matcher.find()) {
            return matcher.group(1); // 返回时间部分
        }
        return "";
    }

    // 解析 # User@Host: ...
    private static Map<String, String> parseUserInfo(String line) {
        Map<String, String> result = new HashMap<>();
        Pattern pattern = Pattern.compile("# User@Host: (\\S+)\\[\\S+\\] @  \\[([\\d\\.]+)\\]  Id: (\\d+)");
        Matcher matcher = pattern.matcher(line);
        if (matcher.find()) {
            result.put("user", matcher.group(1));
            result.put("host", matcher.group(2));
            result.put("sql_id", matcher.group(3));
        }
        return result;
    }

    // 解析 # Query_time: ...
    private static double parseQueryTime(String line) {
        Pattern pattern = Pattern.compile("# Query_time: (\\d+\\.\\d+)");
        Matcher matcher = pattern.matcher(line);
        if (matcher.find()) {
            return Double.parseDouble(matcher.group(1));
        }
        return 0.0;
    }

    // 解析 # Lock_time: ...
    private static double parseLockTime(String line) {
        Pattern pattern = Pattern.compile("Lock_time: (\\d+\\.\\d+)");
        Matcher matcher = pattern.matcher(line);
        if (matcher.find()) {
            return Double.parseDouble(matcher.group(1));
        }
        return 0.0;
    }

    // 解析 # Rows_sent: ...
    private static int parseRowsSent(String line) {
        Pattern pattern = Pattern.compile("Rows_sent: (\\d+)");
        Matcher matcher = pattern.matcher(line);
        if (matcher.find()) {
            return Integer.parseInt(matcher.group(1));
        }
        return 0;
    }

    // 解析 # Rows_examined: ...
    private static int parseRowsExamined(String line) {
        Pattern pattern = Pattern.compile("Rows_examined: (\\d+)");
        Matcher matcher = pattern.matcher(line);
        if (matcher.find()) {
            return Integer.parseInt(matcher.group(1));
        }
        return 0;
    }

    // 解析 SET timestamp = ...
    private static int parseTimestamp(String line) {
        Pattern pattern = Pattern.compile("SET timestamp=(\\d+);");
        Matcher matcher = pattern.matcher(line);
        if (matcher.find()) {
            return Integer.parseInt(matcher.group(1));
        }
        return 0;
    }

    // 插入日志到数据库
    private static void insertLog(Connection conn, String logTime, String user, double queryTime, double lockTime,
                                  int rowsSent, int rowsExamined, String sqlQuery, int timestamp, String host, String sql_id) throws SQLException {
        String sql = "INSERT INTO mysql_log (log_time, user, query_time, lock_time, rows_sent, rows_examined, sql_query, timestamp, host, sql_id) "
                + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, logTime);
            pstmt.setString(2, user);
            pstmt.setDouble(3, queryTime);
            pstmt.setDouble(4, lockTime);
            pstmt.setInt(5, rowsSent);
            pstmt.setInt(6, rowsExamined);
            pstmt.setString(7, sqlQuery);
            pstmt.setInt(8, timestamp);
            pstmt.setString(9, host);
            pstmt.setString(10, sql_id);

            pstmt.executeUpdate();
        }
    }
}